from datetime import datetime

# from tempfile import NamedTemporaryFile
from io import BytesIO
import os
from pathlib import Path
from tempfile import NamedTemporaryFile, TemporaryFile
from typing import Any

from django.core.files import File
from django.core.files.storage import default_storage
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.pagebreak import Break

from naryad.models import Buhreport

month_dict = {
            "01": "Январь",
            "02": "Февраль",
            "03": "Март",
            "04": "Апрель",
            "05": "Май",
            "06": "Июнь",
            "07": "Июль",
            "08": "Август",
            "09": "Сентябрь",
            "10": "Октябрь",
            "11": "Ноябрь",
            "12": "Декабрь",
            }


podr_dict = {
    "165": "Поликлиника №3",
    "3": "Березовское стоматологическое отделение",
    "32": "Яшкинское стоматологическое отделение",
    "245": "Поликлиника №5",
    "243": "Стоматологическое отделение",
    "236": "Поликлиника №4",
    "164": "Поликлиника №2",
    "162": "Поликлиинка №1",
    "28": "Топкинское стоматологическое отделение",
    }


def table_header(sheet, row_number: int, color_fill, spec1: str, spec2: str, podr: str, dzr_end: str)-> tuple[Any, int]:  # noqa: PLR0915
    date_timedate = datetime.strptime(dzr_end, "%d.%m.%Y")
    month_number = str(date_timedate.strftime("%m"))
    year_number = str(date_timedate.strftime("%Y"))
    hinside_number = row_number
    page_break = Break(id=hinside_number)
    sheet.row_breaks.append(page_break)
    hinside_number += 3
    column_number = 1

    sheet.merge_cells(f"A{hinside_number}:F{hinside_number}")
    sheet.cell(row=hinside_number, column=column_number).value = (
        "Главный врач ГАУЗ ККСП, д.м.н"
    )
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    hinside_number += 1

    sheet.merge_cells(f"A{hinside_number}:F{hinside_number}")
    sheet.cell(row=hinside_number, column=column_number).value = (
        "_____________ Д.М.Гарафутдинов"
    )
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    hinside_number += 1

    sheet.merge_cells(f"A{hinside_number}:F{hinside_number}")
    sheet.cell(row=hinside_number, column=column_number).value = (
        f"________________________{year_number}г."
    )
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    hinside_number += 2

    sheet.merge_cells(f"A{hinside_number}:F{hinside_number}")
    sheet.cell(row=hinside_number, column=column_number).value = "Месячный отчет"
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="center", vertical="center",
    )
    hinside_number += 1

    sheet.merge_cells(f"A{hinside_number}:F{hinside_number}")
    sheet.cell(row=hinside_number, column=column_number).value = spec1
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="center", vertical="center",
    )
    hinside_number += 1

    sheet.merge_cells(f"A{hinside_number}:F{hinside_number}")
    string_podr = ""
    string_podr = podr_dict.get(str(podr), "Неизвестно")
    sheet.cell(row=hinside_number, column=column_number).value = string_podr
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="center", vertical="center",
    )
    hinside_number += 1

    sheet.merge_cells(f"A{hinside_number}:F{hinside_number}")
    month = month_dict.get(month_number, "Неизвестно")
    date_string = f"{month} {year_number}"
    string_period = f"За {date_string}г."
    sheet.cell(row=hinside_number, column=column_number).value = string_period
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="center", vertical="center",
    )
    hinside_number += 1

    sheet.cell(row=hinside_number, column=column_number).value = spec2
    sheet.cell(row=hinside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=hinside_number, column=column_number).alignment = Alignment(
        horizontal="left", vertical="center",
    )
    hinside_number += 1

    if spec2== "Врачи":
        sheet.append(
            ["ФИО", "Специальность", "Доходы лаборатории", "", "", "", "Услуги"],
        )
        for i in range(1, 11):
            sheet.cell(row=hinside_number, column=i).font = Font(size=14, bold=True)
            sheet.cell(row=hinside_number, column=i).alignment = Alignment(
                horizontal="center", vertical="center",
            )
            sheet.cell(row=hinside_number, column=i).fill = color_fill
        sheet.append(
            [" ", " ", "Льгота", "ВТ", "ПД", "Итого", "Льгота", "ВТ", "ПД", "Итого"],
        )
        sheet.merge_cells(f"C{hinside_number}:F{hinside_number}")
        sheet.merge_cells(f"G{hinside_number}:J{hinside_number}")
        sheet.merge_cells(f"A{hinside_number}:A{hinside_number+1}")
        sheet.merge_cells(f"B{hinside_number}:B{hinside_number+1}")
        hinside_number += 1

        for i in range(1, 11):
            sheet.cell(row=hinside_number, column=i).font = Font(size=14, bold=True)
            sheet.cell(row=hinside_number, column=i).alignment = Alignment(
                horizontal="center", vertical="center",
            )
            sheet.cell(row=hinside_number, column=i).fill = color_fill
    else:
        sheet.append(["ФИО", "Специальность", "Доходы лаборатории"])
        sheet.append([" ", " ", "Льгота", "ВТ", "ПД", "Итого"])
        sheet.merge_cells(f"C{hinside_number}:F{hinside_number}")
        sheet.merge_cells(f"A{hinside_number}:A{hinside_number+1}")
        sheet.merge_cells(f"B{hinside_number}:B{hinside_number+1}")
        for i in range(1, 7):
            sheet.cell(row=hinside_number, column=i).font = Font(size=14, bold=True)
            sheet.cell(row=hinside_number, column=i).alignment = Alignment(
                horizontal="center", vertical="center",
            )
            sheet.cell(row=hinside_number, column=i).fill = color_fill
        hinside_number += 1

        for i in range(1, 7):
            sheet.cell(row=hinside_number, column=i).font = Font(size=14, bold=True)
            sheet.cell(row=hinside_number, column=i).alignment = Alignment(
                horizontal="center", vertical="center",
            )
            sheet.cell(row=hinside_number, column=i).fill = color_fill
    hinside_number += 1
    return sheet, hinside_number


def table_body(sheet, row_number: int, color_fill, spec_list: list[dict]) -> tuple[Any, int]:  # noqa: PLR0915
    inside_number = row_number
    column_number = 1
    spec_list = sorted(spec_list, key=lambda fio: fio["fio"])
    inside_number -= 1 # Коррекция строки (пустая строка после шапки)
    for spec in spec_list:
        inside_number += 1
        sheet.cell(row=inside_number, column=column_number).value = spec["fio"]
        sheet.cell(row=inside_number, column=column_number).font = Font(
            size=14, bold=False,
        )
        sheet.cell(row=inside_number, column=column_number).alignment = Alignment(
            horizontal="left", vertical="center",
        )
        sheet.cell(row=inside_number, column=column_number + 1).value = spec["dolzh"]
        sheet.cell(row=inside_number, column=column_number + 1).font = Font(
            size=14, bold=False,
        )
        sheet.cell(row=inside_number, column=column_number + 1).alignment = (
            Alignment(horizontal="right", vertical="center")
        )
        sheet.cell(row=inside_number, column=column_number + 2).value = spec[
            "lab_lgot"
        ]
        sheet.cell(row=inside_number, column=column_number + 2).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 2).font = Font(
            size=14, bold=False,
        )
        sheet.cell(row=inside_number, column=column_number + 2).alignment = (
            Alignment(horizontal="right", vertical="center")
        )
        sheet.cell(row=inside_number, column=column_number + 3).value = spec[
            "lab_vt"
        ]
        sheet.cell(row=inside_number, column=column_number + 3).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 3).font = Font(
            size=14, bold=False,
        )
        sheet.cell(row=inside_number, column=column_number + 3).alignment = (
            Alignment(horizontal="right", vertical="center")
        )
        sheet.cell(row=inside_number, column=column_number + 4).value = spec[
            "lab_pd"
        ]
        sheet.cell(row=inside_number, column=column_number + 4).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 4).font = Font(
            size=14, bold=False,
        )
        sheet.cell(row=inside_number, column=column_number + 4).alignment = (
            Alignment(horizontal="right", vertical="center")
        )
        sheet.cell(row=inside_number, column=column_number + 5).value = spec["lab_itog"]
        sheet.cell(row=inside_number, column=column_number + 5).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 5).font = Font(
            size=14, bold=True,
        )
        sheet.cell(row=inside_number, column=column_number + 5).alignment = (
            Alignment(horizontal="right", vertical="center")
        )
        if "usl_itog" in spec and spec["usl_itog"] > 0:
            sheet.cell(row=inside_number, column=column_number + 6).value = spec["usl_lgot"]
            sheet.cell(row=inside_number, column=column_number + 6).number_format = (
                "#,###,##0.00"
            )
            sheet.cell(row=inside_number, column=column_number + 6).font = Font(
                size=14, bold=True,
            )
            sheet.cell(row=inside_number, column=column_number + 6).alignment = (
                Alignment(horizontal="right", vertical="center")
            )
            sheet.cell(row=inside_number, column=column_number + 7).value = spec["usl_vt"]
            sheet.cell(row=inside_number, column=column_number + 7).number_format = (
                "#,###,##0.00"
            )
            sheet.cell(row=inside_number, column=column_number + 7).font = Font(
                size=14, bold=True,
            )
            sheet.cell(row=inside_number, column=column_number + 7).alignment = (
                Alignment(horizontal="right", vertical="center")
            )
            sheet.cell(row=inside_number, column=column_number + 8).value = spec["usl_pd"]
            sheet.cell(row=inside_number, column=column_number + 8).number_format = (
                "#,###,##0.00"
            )
            sheet.cell(row=inside_number, column=column_number + 8).font = Font(
                size=14, bold=True,
            )
            sheet.cell(row=inside_number, column=column_number + 8).alignment = (
                Alignment(horizontal="right", vertical="center")
            )
            sheet.cell(row=inside_number, column=column_number + 9).value = spec["usl_itog"]
            sheet.cell(row=inside_number, column=column_number + 9).number_format = (
                "#,###,##0.00"
            )
            sheet.cell(row=inside_number, column=column_number + 9).font = Font(
                size=14, bold=True,
            )
            sheet.cell(row=inside_number, column=column_number + 9).alignment = (
                Alignment(horizontal="right", vertical="center")
            )

    inside_number += 1
    sheet.cell(row=inside_number, column=column_number).value = "Итого"
    sheet.cell(row=inside_number, column=column_number).font = Font(size=14, bold=True)
    sheet.cell(row=inside_number, column=column_number).fill = color_fill
    sheet.cell(row=inside_number, column=column_number + 1).fill = color_fill
    sheet.cell(row=inside_number, column=column_number + 2).value = sum(x["lab_lgot"] for x in spec_list)
    sheet.cell(row=inside_number, column=column_number + 2).number_format = (
        "#,###,##0.00"
    )
    sheet.cell(row=inside_number, column=column_number + 2).font = Font(
        size=14, bold=True,
    )
    sheet.cell(row=inside_number, column=column_number + 2).fill = color_fill
    sheet.cell(row=inside_number, column=column_number + 2).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    sheet.cell(row=inside_number, column=column_number + 3).value = sum(x["lab_vt"] for x in spec_list)
    sheet.cell(row=inside_number, column=column_number + 3).number_format = (
        "#,###,##0.00"
    )
    sheet.cell(row=inside_number, column=column_number + 3).font = Font(
        size=14, bold=True,
    )
    sheet.cell(row=inside_number, column=column_number + 3).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    sheet.cell(row=inside_number, column=column_number + 3).fill = color_fill
    sheet.cell(row=inside_number, column=column_number + 4).value = sum(x["lab_pd"] for x in spec_list)
    sheet.cell(row=inside_number, column=column_number + 4).number_format = (
        "#,###,##0.00"
    )
    sheet.cell(row=inside_number, column=column_number + 4).font = Font(
        size=14, bold=True,
    )
    sheet.cell(row=inside_number, column=column_number + 4).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    sheet.cell(row=inside_number, column=column_number + 4).fill = color_fill
    sheet.cell(row=inside_number, column=column_number + 5).value = sum(x["lab_itog"] for x in spec_list)
    sheet.cell(row=inside_number, column=column_number + 5).number_format = (
        "#,###,##0.00"
    )
    sheet.cell(row=inside_number, column=column_number + 5).font = Font(
        size=14, bold=True,
    )
    sheet.cell(row=inside_number, column=column_number + 5).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    sheet.cell(row=inside_number, column=column_number + 5).fill = color_fill
    is_usl = False
    for one in spec_list:
        if "usl_itog" in one and one["usl_itog"] > 0:
            is_usl = True
            break
    if is_usl:
        sheet.cell(row=inside_number, column=column_number + 6).value = sum(x["usl_lgot"] for x in spec_list)
        sheet.cell(row=inside_number, column=column_number + 6).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 6).font = Font(
            size=14, bold=True,
        )
        sheet.cell(row=inside_number, column=column_number + 6).alignment = Alignment(
            horizontal="right", vertical="center",
        )
        sheet.cell(row=inside_number, column=column_number + 6).fill = color_fill
        sheet.cell(row=inside_number, column=column_number + 7).value = sum(x["usl_vt"] for x in spec_list)
        sheet.cell(row=inside_number, column=column_number + 7).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 7).font = Font(
            size=14, bold=True,
        )
        sheet.cell(row=inside_number, column=column_number + 7).alignment = Alignment(
            horizontal="right", vertical="center",
        )
        sheet.cell(row=inside_number, column=column_number + 7).fill = color_fill
        sheet.cell(row=inside_number, column=column_number + 8).value = sum(x["usl_pd"] for x in spec_list)
        sheet.cell(row=inside_number, column=column_number + 8).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 8).font = Font(
            size=14, bold=True,
        )
        sheet.cell(row=inside_number, column=column_number + 8).alignment = Alignment(
            horizontal="right", vertical="center",
        )
        sheet.cell(row=inside_number, column=column_number + 8).fill = color_fill
        sheet.cell(row=inside_number, column=column_number + 9).value = sum(x["usl_itog"] for x in spec_list)
        sheet.cell(row=inside_number, column=column_number + 9).number_format = (
            "#,###,##0.00"
        )
        sheet.cell(row=inside_number, column=column_number + 9).font = Font(
            size=14, bold=True,
        )
        sheet.cell(row=inside_number, column=column_number + 9).alignment = Alignment(
            horizontal="right", vertical="center",
        )
        sheet.cell(row=inside_number, column=column_number + 9).fill = color_fill
    inside_number += 1
    return sheet, inside_number


def table_footer(sheet, row_number: int, dzr_start: str, dzr_end: str, dzr_lgot_start: str, dzr_lgot_end: str, podr_for_print: str, spec_isp: str) -> int:
    column_number = 1
    fnumber = row_number
    sheet.merge_cells(f"A{fnumber}:F{fnumber}")
    sheet.cell(row=fnumber, column=column_number).value = (
        f"Отчет по закрытым нарядам за период: Платные с {dzr_start} по {dzr_end}, Льготные с {dzr_lgot_start} по {dzr_lgot_end}, {podr_for_print}"
    )
    sheet.cell(row=fnumber, column=column_number).font = Font(
        size=13, bold=False,
    )
    sheet.cell(row=fnumber, column=column_number).alignment = Alignment(
        horizontal="left", vertical="center",
    )
    fnumber += 2
    sheet.merge_cells(f"A{fnumber}:B{fnumber}")
    sheet.merge_cells(f"C{fnumber}:E{fnumber}")
    sheet.cell(row=fnumber, column=column_number).value = (
        f"{spec_isp}________________________________________(ФИО)"
    )
    sheet.cell(row=fnumber, column=column_number).font = Font(
        size=14, bold=False,
    )
    sheet.cell(row=fnumber, column=column_number).alignment = Alignment(
        horizontal="left", vertical="center",
    )
    sheet.cell(row=fnumber, column=column_number + 2).value = (
        "________________________(подпись)"
    )
    sheet.cell(row=fnumber, column=column_number + 2).font = Font(
        size=14, bold=False,
    )
    sheet.cell(row=fnumber, column=column_number + 2).alignment = Alignment(
        horizontal="left", vertical="center",
    )
    sheet.cell(row=fnumber, column=column_number + 5).value = (
        f"""{(datetime.now()).strftime('%d-%m-%Y')}г."""  # noqa: DTZ005
    )
    sheet.cell(row=fnumber, column=column_number + 5).font = Font(
        size=14, bold=False,
    )
    sheet.cell(row=fnumber, column=column_number + 5).alignment = Alignment(
        horizontal="right", vertical="center",
    )
    fnumber += 1
    return sheet, fnumber


def table_gen(  # noqa: PLR0913
    sheet,
    row_number: int,
    color_fill: PatternFill,
    spec1:str, spec2:str,
    dzr_start,
    dzr_end,
    dzr_lgot_start,
    dzr_lgot_end,
    podr_for_print : str,
    spec_list: list[dict],
    spec_isp: str,
    podr: str) -> tuple[Any, int]:
    grow_number = row_number
    sheet, grow_number = table_header(
        sheet=sheet,
        row_number=grow_number,
        spec1=spec1,
        spec2=spec2,
        podr=podr,
        dzr_end=dzr_end,
        color_fill=color_fill,
        )

    sheet, grow_number = table_body(
        sheet=sheet,
        row_number=grow_number,
        color_fill=color_fill,
        spec_list=spec_list,
        )

    sheet, grow_number = table_footer(
        sheet=sheet,
        row_number=grow_number,
        podr_for_print=podr_for_print,
        dzr_start=dzr_start,
        dzr_end=dzr_end,
        dzr_lgot_start=dzr_lgot_start,
        dzr_lgot_end=dzr_lgot_end,
        spec_isp=spec_isp,
        )

    return sheet, grow_number

ORTOPED = 2
ORTODONT = 3

def to_xls(  # noqa: PLR0913, PLR0915
    report: Buhreport,
    dzr_start,
    dzr_end,
    dzr_lgot_start,
    dzr_lgot_end,
    list_docs,
    list_techs,
    list_pols,
    list_lits,
    list_cadcams,
    list_zp_techs,
    list_zp_pols,
    list_zp_lits,
    list_zp_cadcams,
    podr : str = "",
) -> None:
    dzr_start = datetime.strptime(dzr_start, "%Y-%m-%d")
    dzr_start = dzr_start.strftime("%d.%m.%Y")
    dzr_end = datetime.strptime(dzr_end, "%Y-%m-%d")
    dzr_end = dzr_end.strftime("%d.%m.%Y")
    dzr_lgot_start = datetime.strptime(dzr_lgot_start, "%Y-%m-%d")
    dzr_lgot_start = dzr_lgot_start.strftime("%d.%m.%Y")
    dzr_lgot_end = datetime.strptime(dzr_lgot_end, "%Y-%m-%d")
    dzr_lgot_end = dzr_lgot_end.strftime("%d.%m.%Y")
    color_fill: PatternFill = PatternFill(
        start_color="7abaeb", end_color="7abaeb", fill_type="solid",
    )
    wb = Workbook()
    sheet = wb.active
    sheet.page_setup.orientation = sheet.ORIENTATION_LANDSCAPE
    sheet.page_setup.paperSize = sheet.PAPERSIZE_A4
    sheet.sheet_properties.pageSetUpPr.fitToPage = True
    sheet.page_setup.fitToWidth = True
    sheet.title = "Ортопеды"
    sheet.page_setup.fitToWidth = True
    sheet.page_setup.orientation = sheet.ORIENTATION_LANDSCAPE
    sheet.page_margins.left = 0.3
    sheet.page_margins.right = 0.1
    sheet.page_margins.top = 0.4
    sheet.page_margins.bottom = 0.44
    sheet.page_margins.header = 0.2
    string_podr = podr_dict.get(str(report.podr), "Неизвестно")
    podr_for_print = "Подразделение: Все"
    if report.podr > 0:
        # sheet.append([f"Подразделение: {string_podr}"])
        podr_for_print = f"Подразделение: {string_podr}"
    else:
        sheet.append(["Подразделение: Все"])
    row_number = 1

    if report.profile == ORTOPED:
        spec_isp = "Заведующий(ая) ортопедическим отделением"
    if report.profile == ORTODONT:
        spec_isp = "Заведующий(ая) ортопедическим отделением"

    if len(list_docs) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Врачей",
            spec2="Врачи",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_docs,
            spec_isp=spec_isp,
            )
    if len(list_techs) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Зубных техников",
            spec2="Зубные техники",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_techs,
            spec_isp="Старший зубной техник",
            )
    if len(list_pols) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Полировщиков",
            spec2="Полировщики",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_pols,
            spec_isp="Старший зубной техник",
            )
    if len(list_lits) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Литейщиков",
            spec2="Литейщики",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_lits,
            spec_isp="Старший зубной техник",
        )
    if len(list_cadcams) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Операторов CAD/CAM",
            spec2="Операторы CAD/CAM",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_cadcams,
            spec_isp="Старший зубной техник",
        )
    if len(list_zp_techs) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Зарплата Зубных техников",
            spec2="Зубные техники",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_zp_techs,
            spec_isp="Старший зубной техник",
        )
    if len(list_zp_pols) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Зарплата Полировщиков",
            spec2="Полировщики",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_zp_pols,
            spec_isp="Старший зубной техник",
        )
    if len(list_zp_lits) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Зарплата Литейщиков",
            spec2="Литейщики",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_zp_lits,
            spec_isp="Старший зубной техник",
        )
    if len(list_zp_cadcams) > 0:
        sheet, row_number = table_gen(
            sheet=sheet,
            row_number=row_number,
            color_fill=color_fill,
            spec1="Зарплата Операторов CAD/CAM",
            spec2="Операторы CAD/CAM",
            podr=report.podr,
            dzr_start=dzr_start,
            dzr_end=dzr_end,
            dzr_lgot_start=dzr_lgot_start,
            dzr_lgot_end=dzr_lgot_end,
            podr_for_print=podr_for_print,
            spec_list=list_zp_cadcams,
            spec_isp="Старший зубной техник",
        )

    sheet.column_dimensions["A"].width = 60
    sheet.column_dimensions["B"].width = 30
    sheet.column_dimensions["C"].width = 20
    sheet.column_dimensions["D"].width = 17
    sheet.column_dimensions["E"].width = 17
    sheet.column_dimensions["F"].width = 17
    sheet.column_dimensions["G"].width = 17
    sheet.column_dimensions["H"].width = 17
    sheet.column_dimensions["I"].width = 17
    sheet.column_dimensions["J"].width = 17

    filename = f"{report.year}.{report.month}.{report.podr}.{report.profile}.xlsx"
    wb.save(filename)
    with Path(filename).open("rb") as f:
        report.file = File(f, name=f"{report.year}.{report.month}.{report.podr}.{report.profile}.xlsx")
        report.save()
    Path(filename).unlink()


def set_column_widths(worksheet) -> None:
    column_widths = []
    try:
        for row in worksheet.iter_rows():
            for i, cell in enumerate(row):
                    column_widths[i] = max(column_widths[i], len(str(cell.value)) + 3)
    except IndexError:
        column_widths.append(len(str(cell.value)))
    for i, column_width in enumerate(column_widths):
        worksheet.column_dimensions[get_column_letter(i + 1)].width = column_width + 10
